AWS Database Migration Serviceを使ったMySQLからRedshiftへのレプリケーション手順 まとめ

はじめに

AWSのDMS(Database Migration Service)はデータ移行のためのサービスですが、継続的レプリケーションすることも可能です。基幹DBから分析用DBへレプリケーションしたいという需要は多いと思いますので、MySQLからRedshiftへの継続的データレプリケーションする場合の手順をまとめてました。MySQLのデータを更新すると数秒後にRedshiftも更新されます。

1.MySQLを準備する

まずは複製されるDBの準備をします。私はRDSでMySQLを構築しました。VPC内から接続できるようにSecurity Groupの設定をしておいて下さい。 MySQLを複製元にしてレプリケーションするためにはパラメータグループの以下の項目を変更する必要があります。 設定を変更できたら、RDSを再起動して反映させてください。

項目	設定値
binlog_format	row
binlog_checksum	none

準備ができたので複製するテーブルを作成しましょう。今回は私が趣味でやっているポケモンGOで捕まえたポケモンの名前、CP、タイプ情報が入った pokemon_go というテーブルを作成してみます。

-- テーブルを作成
create table pokemon_go (
  pokemon_id int primary key,
  pokemon_name varchar(255) not null,
  cp int not null,
  pokemon_type varchar(255) not null
) default charset=utf8;

-- レコードを挿入
insert into pokemon_go values(134, 'シャワーズ', 2063, 'みず');
insert into pokemon_go values(59,  'ウインディ', 1711, 'ほのお');
insert into pokemon_go values(135, 'サンダース', 1586, 'でんき');
select * from pokemon_go;

2.Redshiftを準備する

次にRedshiftのクラスターを作成してください。 VPC内から接続できるようにSecurity Groupの設定をしておいて下さい。

3.Replication Instanceを準備する

ここからはDMSの画面で設定を行います。まずはレプリケーションの処理を行う仮想マシンであるReplication Instanceの準備を行います。

MySQLからRedshiftにレプリケーションするインスタンスを立ち上げます。 Instance classをsmallにした以外はデフォルトです。

4.Endpointを登録する

MySQLとRedshiftをEndpointとして登録します。

Endpoint Typeですが今回はMySQLはSource、RedshiftはTargetになります。 基本的には接続情報を入れるだけです。登録できたら先ほど準備したReplication Instanceから接続できるかをテストしましょう。

MySQLの場合

Redshiftの場合

5.Taskを作成する

最後にTaskを作成します。 左端のメニューからTasksを選択するとTaskの一覧が表示されます。表の上にあるCreate Taskボタンをクリックしてください。

以下のような画面になりますので、２つのEndpointとReplication Instanceを紐づけます。 Migration Typeを以下の3種類から選択します。今回はレプリケーション機能を使うのでMigrate existing data and replicate ongoing changes onlyを選択します。

• Migrate existing data：1回だけにデータ移行する
• Replicate ongoing changes only：Source更新時に差分をTargetに反映させる
• Migrate existing data and replicate ongoing changes only：データ移行後、Source更新時に差分をTargetに反映させる

Create taskボタンを押すとタスクが起動します。以下のようにLoad CompeleteというステータスになったらRedshiftにデータが入っていると思いますので見てみましょう。

6.同期されることを確認する

すでにRedshiftにテーブルが作成され、データが複製されていると思いますので確認してみましょう。MySQLのDB名がRedshiftのスキーマ名になっていました。 次はMySQLが更新されたRedshiftに反映されるか試してみましょう。MySQLで以下のSQLを実行してみてください。

insert into pokemon_go values(112, 'サイドン', 1252, 'じめん');

insertだけでなくdelete と updateも試しましたが数秒で反映されていることを確認しました。

7.Redshift用のテーブルを作成する

DMSがRedshiftに作成したテーブルの定義をよく見ると、VARCHARの長さが765になっていました。どうもMySQLからRedshiftに変換する際に3倍にしているようです。 MySQLのVARCHARは定義時に文字数を指定しますがRedshiftはバイト数での指定になります。必要に応じて適切なサイズに変換しましょう。 またRedshiftのテーブルには分散キーやソートキー、圧縮エンコードを設定する必要があります。 Redshift側のテーブルを以下のように新しいテーブルを作成し、データを入れ替えてみましょう。

create table mydb.pokemon_go_new
(
   pokemon_id    integer        not null,
   pokemon_name  varchar(1020)   not null,
   cp            integer        not null,
   pokemon_type  varchar(1020)   not null,
   primary key(pokemon_id)
) distkey (pokemon_id)
  sortkey(pokemon_id);
  
  
insert into mydb.pokemon_go_new select * from mydb.pokemon_go;
alter table mydb.pokemon_go rename to pokemon_go_old;
alter table mydb.pokemon_go_new  rename to pokemon_go;
drop table mydb.pokemon_go_old;

Redshift側のテーブルを入れ替えた後に以下のSQLでMySQLにデータを追加しましたが、数秒後問題なくRedshift側にも反映されていることを確認しました。

insert into pokemon_go values(68, 'カイリキー', 1532, 'かくとう');

最後に

今回調べたことは以上になります。実案件で継続的レプリケーションを使うには以下のようなことが気になりました。

• 全てのテーブルではなく移行対象のテーブルを絞りたい場合はどうするのか。
• MySQLが文字コードがSJISでも問題ないか。
• Redshiftのスキーマをユーザが決めたい場合はどうするのか。
• Redshiftにないデータ型を使った場合どうなるのか。
• Redshiftはデータをためてから一度に処理するほうが得意なので更新期間を長くしたいが、そういった場合はどうするのか。

次回はこのあたりを詳しく調べてみたいと思います。